package com.bainaeco.framework.base.dao;

import java.beans.PropertyDescriptor;
import java.lang.reflect.ParameterizedType;
import java.math.BigDecimal;
import java.sql.Time;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.PropertyAccessorFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.bainaeco.framework.base.model.BaseModel;
import com.bainaeco.framework.common.jdbc.Jdbc;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.BaseModelTableName;
import com.bainaeco.framework.enums.IsDelete;
import com.bainaeco.framework.util.DBKey;
import com.bainaeco.framework.util.paging.ArrayPagingList;
import com.bainaeco.framework.util.paging.PagingList;
import com.bainaeco.framework.util.paging.Sort;

@Transactional(readOnly = true)
public abstract class AbstractBaseDAO<T extends BaseModel> extends Jdbc {

	private static final String MYSQL_DIALECT = "MySQLDialect";
	private static final String DB2_DIALECT = "DB2Dialect";

	private Log logger = LogFactory.getLog(this.getClass());

	protected Class<T> baseModelClass;
	protected String tableName;

	@Value("${jdbc.dialect:" + MYSQL_DIALECT + "}")
	private String dialect;

	private boolean existBaseTable;

	private String baseTableIdName;

	@Override
	@PostConstruct
	@SuppressWarnings("unchecked")
	public void init() {
		super.init();

		this.baseModelClass = (Class<T>) ((ParameterizedType) getClass().getGenericSuperclass())
				.getActualTypeArguments()[0];
		this.tableName = underscoreName(baseModelClass.getSimpleName());
		// system_info_lang => system_info_id
		if (isExistBaseTable()) {
			existBaseTable = true;
			this.baseTableIdName = new StringBuilder(this.tableName.substring(0, this.tableName.lastIndexOf("_") + 1))
					.append("id").toString();
		}
	}

	@PreDestroy
	public void destroy() {
		logger.info("destroy");
	}

	private boolean isExistBaseTable() {
		return this.tableName.lastIndexOf("_") != -1;
	}

	/**
	 * Convert a name in camelCase to an underscored name in lower case. Any
	 * upper case letters are converted to lower case with a preceding
	 * underscore.
	 *
	 * @param name
	 *            the string containing original name
	 * @return the converted name
	 */
	protected String underscoreName(final String name) {
		final StringBuilder result = new StringBuilder();

		result.append("`");
		if (name != null && name.length() > 0) {
			result.append(name.substring(0, 1).toLowerCase());
			for (int i = 1; i < name.length(); i++) {
				final String s = name.substring(i, i + 1);
				if (s.equals(s.toUpperCase(Locale.ENGLISH))) {
					result.append("_");
					result.append(s.toLowerCase());
				} else {
					result.append(s);
				}
			}
		}
		result.append("`");
		return result.toString();
	}

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public T insert(T object) {
		object.setId(DBKey.getUUID());
		object.setCreatedTime(new Date());
		final SqlStatementParameter sqlStatementParameter = addSqlStatementParameter(object);
		super.updateForBoolean(sqlStatementParameter.getSql(), sqlStatementParameter.getParam());
		// object.setId(super.insertForKey(sqlStatementParameter.getSql(),
		// sqlStatementParameter.getParam()));
		return object;
	}

	@SuppressWarnings("rawtypes")
	private SqlStatementParameter addSqlStatementParameter(final T object) {
		final StringBuilder sql = new StringBuilder("INSERT INTO ").append(this.tableName).append("(");
		final StringBuilder placeholder = new StringBuilder();
		final StatementParameter param = new StatementParameter();

		final PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(baseModelClass);
		final BeanWrapper beanWrapper = PropertyAccessorFactory.forBeanPropertyAccess(object);

		boolean flag = false;
		for (final PropertyDescriptor pd : pds) {
			final String propertyName = pd.getName();
			final String underscoredName = underscoreName(propertyName);
			final Class propertyType = beanWrapper.getPropertyType(propertyName);
			final Object propertyValue = beanWrapper.getPropertyValue(propertyName);

			if (!propertyName.equals("class")) {
				if (propertyValue != null) {
					if (flag) {
						sql.append(",");
						placeholder.append(",");
					}
					sql.append(underscoredName);
					placeholder.append("?");
					if (propertyType.equals(Time.class)) {
						param.setTime((Time) propertyValue);
					} else if (propertyType.equals(Date.class)) {
						param.setDate((Date) propertyValue);
					} else if (propertyType.equals(String.class)) {
						param.setString((String) propertyValue);
					} else if (propertyType.equals(Boolean.class)) {
						param.setBool((Boolean) propertyValue);
					} else if (propertyType.equals(Integer.class)) {
						param.setInt((Integer) propertyValue);
					} else if (propertyType.equals(Long.class)) {
						param.setLong((Long) propertyValue);
					} else if (propertyType.equals(Double.class)) {
						param.setDouble((Double) propertyValue);
					} else if (propertyType.equals(Float.class)) {
						param.setFloat((Float) propertyValue);
					} else {
						param.setString(propertyValue.toString());
					}
					flag = true;
				} 
			}
		}

		sql.append(") VALUES(");
		sql.append(placeholder);
		sql.append(")");

		return new SqlStatementParameter(sql.toString(), param);
	}

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public boolean update(T object) {
		object.setUpdatedTime(new Date());
		final SqlStatementParameter updateSqlStatementParameter = updateSqlStatementParameter(object, false);
		if (updateSqlStatementParameter.getParam().size() > 0) {
			System.out.println(updateSqlStatementParameter.getSql());
			return super.updateForBoolean(updateSqlStatementParameter.getSql(), updateSqlStatementParameter.getParam());
		} else {
			return false;
		}
	}
	
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public boolean update(T object, boolean isSetNull) {
		object.setUpdatedTime(new Date());
		final SqlStatementParameter updateSqlStatementParameter = updateSqlStatementParameter(object, isSetNull);
		if (updateSqlStatementParameter.getParam().size() > 0) {
			return super.updateForBoolean(updateSqlStatementParameter.getSql(), updateSqlStatementParameter.getParam());
		} else {
			return false;
		}
	}

	@SuppressWarnings("rawtypes")
	private SqlStatementParameter updateSqlStatementParameter(final T object, boolean isSetNull) {
		final StringBuilder sql = new StringBuilder("UPDATE ").append(this.tableName).append(" SET");
		final StatementParameter param = new StatementParameter();

		final PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(baseModelClass);
		final BeanWrapper beanWrapper = PropertyAccessorFactory.forBeanPropertyAccess(object);
		boolean flag = false;
		for (final PropertyDescriptor pd : pds) {
			final String propertyName = pd.getName();
			final String underscoredName = underscoreName(propertyName);
			final Class propertyType = beanWrapper.getPropertyType(propertyName);
			final Object propertyValue = beanWrapper.getPropertyValue(propertyName);

			if (!propertyName.equals("id") && !propertyName.equals("class")) {
				if(isSetNull){
					if (flag) {
						sql.append(",");
					}
					sql.append(" " + underscoredName + "=?");
					if (propertyValue != null) {

						if (propertyType.equals(Time.class)) {
							param.setTime((Time) propertyValue);
						} else if (propertyType.equals(Date.class)) {
							param.setDate((Date) propertyValue);
						} else if (propertyType.equals(String.class)) {
							param.setString((String) propertyValue);
						} else if (propertyType.equals(Boolean.class)) {
							param.setBool((Boolean) propertyValue);
						} else if (propertyType.equals(Integer.class)) {
							param.setInt((Integer) propertyValue);
						} else if (propertyType.equals(Long.class)) {
							param.setLong((Long) propertyValue);
						} else if (propertyType.equals(Double.class)) {
							param.setDouble((Double) propertyValue);
						} else if (propertyType.equals(Float.class)) {
							param.setFloat((Float) propertyValue);
						} else {
							param.setString(propertyValue.toString());
						}
					} else {
						param.setNull();
					}
					flag = true;
				}else{
					if (propertyValue != null) {
						if (flag) {
							sql.append(",");
						}
						sql.append(" " + underscoredName + "=?");
						if (propertyType.equals(Time.class)) {
							param.setTime((Time) propertyValue);
						} else if (propertyType.equals(Date.class)) {
							param.setDate((Date) propertyValue);
						} else if (propertyType.equals(String.class)) {
							param.setString((String) propertyValue);
						} else if (propertyType.equals(Boolean.class)) {
							param.setBool((Boolean) propertyValue);
						} else if (propertyType.equals(Integer.class)) {
							param.setInt((Integer) propertyValue);
						} else if (propertyType.equals(Long.class)) {
							param.setLong((Long) propertyValue);
						} else if (propertyType.equals(Double.class)) {
							param.setDouble((Double) propertyValue);
						} else if (propertyType.equals(Float.class)) {
							param.setFloat((Float) propertyValue);
						} else {
							param.setString(propertyValue.toString());
						}
						flag = true;
					}
				}
			}
		}

		sql.append(" WHERE id=?");
		param.setString(object.getId());

		return new SqlStatementParameter(sql.toString(), param);
	}

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public boolean updateToDelete(final String id) {
		final StringBuilder sql = new StringBuilder("UPDATE ").append(this.tableName).append(" SET ");
		sql.append(BaseModelTableName.IS_DELETE.toStringValue()).append(" = ?");
		sql.append(" WHERE ").append(BaseModelTableName.ID.toStringValue()).append(" = ?");
		final StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.YES.toIntValue());
		param.setString(id);
		return super.updateForBoolean(sql.toString(), param);
	}

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public boolean updateToRestore(final String id) {
		final StringBuilder sql = new StringBuilder("UPDATE ").append(this.tableName).append(" SET ");
		sql.append(BaseModelTableName.IS_DELETE.toStringValue()).append(" = ?");
		sql.append(" WHERE ").append(BaseModelTableName.ID.toStringValue()).append(" = ?");
		final StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(id);
		return super.updateForBoolean(sql.toString(), param);
	}

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public boolean delete(final T object) {
		return delete(object.getId());
	}

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public boolean delete(final String id) {
		final StringBuilder sql = new StringBuilder("DELETE FROM ").append(this.tableName).append(" WHERE id = ?");
		final StatementParameter param = new StatementParameter();
		param.setString(id);
		return super.updateForBoolean(sql.toString(), param);
	}

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public boolean deleteByIds(final Collection<Object> keys) {
		final StringBuilder sql = new StringBuilder("DELETE FROM ").append(this.tableName)
				.append(" WHERE ID in (:keys)");
		final Map<String, Collection<Object>> paramMap = Collections.singletonMap("keys", keys);
		return super.updateForRecordWithNamedParam(sql.toString(), paramMap) > 0;
	}

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public int batchInsert(List<T> list) {
		int iRet = 0;
		String sql = "";
		final List<StatementParameter> paramList = new ArrayList<StatementParameter>();

		if (list != null && !list.isEmpty()) {
			for (final T t : list) {
				t.setId(DBKey.getUUID());
				final SqlStatementParameter addSqlStatementParameter = addSqlStatementParameter(t);
				sql = addSqlStatementParameter.getSql();
				paramList.add(addSqlStatementParameter.getParam());
			}

			final int[] result = super.batchUpdate(sql, paramList);

			if (result != null && result.length > 0) {
				for (final int i : result) {
					iRet += i;
				}
			}
		}

		return iRet;
	}

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public int batchUpdate(List<T> list) {
		int iRet = 0;
		String sql = "";
		final List<StatementParameter> paramList = new ArrayList<StatementParameter>();

		if (list != null && !list.isEmpty()) {
			for (final T t : list) {
				final SqlStatementParameter updateSqlStatementParameter = updateSqlStatementParameter(t , false);
				sql = updateSqlStatementParameter.getSql();
				paramList.add(updateSqlStatementParameter.getParam());
			}

			final int[] result = super.batchUpdate(sql, paramList);

			if (result != null && result.length > 0) {
				for (final int i : result) {
					iRet += i;
				}
			}
		}

		return iRet;
	}
	
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public int batchUpdate(List<T> list, boolean isSetNull) {
		int iRet = 0;
		String sql = "";
		final List<StatementParameter> paramList = new ArrayList<StatementParameter>();

		if (list != null && !list.isEmpty()) {
			for (final T t : list) {
				final SqlStatementParameter updateSqlStatementParameter = updateSqlStatementParameter(t , isSetNull);
				sql = updateSqlStatementParameter.getSql();
				paramList.add(updateSqlStatementParameter.getParam());
			}

			final int[] result = super.batchUpdate(sql, paramList);

			if (result != null && result.length > 0) {
				for (final int i : result) {
					iRet += i;
				}
			}
		}

		return iRet;
	}

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public boolean batchUpdateToDelete(final String... ids) {
		final StringBuilder sql = new StringBuilder("UPDATE ").append(this.tableName).append(" SET ")
				.append(BaseModelTableName.IS_DELETE.toStringValue()).append(" = ?").append(" WHERE id IN (");
		final StatementParameter param = new StatementParameter();
		param.setInt(IsDelete.YES.toIntValue());
		for (int i = 0, len = ids.length; i < len; i++) {
			sql.append("?");
			if (i < len - 1) {
				sql.append(",");
			}
			param.setString(ids[i]);
		}
		sql.append(")");

		return super.updateForBoolean(sql.toString(), param);
	}

	/**
	 * Query model data by id.
	 * 
	 * @param id
	 *            : id value
	 * @return
	 */
	public T query(final String id) {
		final StringBuilder sql = new StringBuilder("SELECT * FROM ").append(this.tableName).append(" WHERE id = ?");
		final StatementParameter param = new StatementParameter();
		param.setString(id);
		return super.query(sql.toString(), this.baseModelClass, param);
	}

	public List<T> findByBaseId(final String id) {
		if (existBaseTable) {
			final StringBuilder sql = new StringBuilder("SELECT * FROM ").append(this.tableName).append(" WHERE ")
					.append(this.baseTableIdName).append(" = ?");
			final StatementParameter param = new StatementParameter();
			param.setString(id);
			return super.queryForList(sql.toString(), this.baseModelClass, param);
		} else {
			logger.error("Base Table not found");
			return null;
		}
	}

	public List<T> findAll() {
		final StringBuilder sql = new StringBuilder("SELECT * FROM ").append(this.tableName);
		return super.queryForList(sql.toString(), this.baseModelClass);
	}

	public List<T> findByIds(final Collection<Object> keys) {
		final StringBuilder sql = new StringBuilder("SELECT * FROM ").append(this.tableName)
				.append(" WHERE ID in (:keys)");
		final Map<String, Collection<Object>> paramMap = Collections.singletonMap("keys", keys);
		return super.queryForListWithNamedParam(sql.toString(), this.baseModelClass, paramMap);
	}

	/**
	 * Get paging list
	 *
	 * @param object
	 * @param criteria
	 * @param args
	 * @return
	 */
	@SuppressWarnings("hiding")
	public <T> PagingList<T> queryForPagingList(final String sql, final Class<T> elementType, StatementParameter param,
			final Sort sort, final int... args) {
		final PagingList<T> resultList = new ArrayPagingList<T>();

		if (args == null || args.length < 2) {
			resultList.addAll(queryForList(sql, elementType, param));
			return resultList;
		}

		int startRecord = 0;
		if (args[0] > 0) {
			startRecord = args[0];
		}
		int maxRecords = 1;
		if (args[1] > 1) {
			maxRecords = args[1];
		}

		final int page = startRecord / maxRecords;

		final StringBuilder countSql = new StringBuilder("SELECT COUNT(*) FROM (").append(sql).append(") a");
		if (param == null) {
			param = new StatementParameter();
		}
		final int totalRecords = super.queryForInt(countSql.toString(), param);

		StringBuilder pagingSql = new StringBuilder("SELECT a.* FROM (").append(sql).append(") a");

		pagingSql.append(" ORDER BY ").append(underscoreName(sort.getColumn())).append(" ").append(sort.getSortType());

		if (MYSQL_DIALECT.equals(dialect)) {
			pagingSql.append(" LIMIT ?, ?");
			param.setInt(startRecord);
			param.setInt(maxRecords);
		} else if (DB2_DIALECT.equals(dialect)) {
			final StringBuilder db2sql = new StringBuilder();
			db2sql.append("select * from (select ROW_NUMBER() OVER() AS ROWNUM, source.*  from (");
			db2sql.append(sql);
			db2sql.append(") source ) a where ROWNUM > ? and ROWNUM <= ?");

			pagingSql = db2sql;
			param.setInt(startRecord);
			param.setInt(startRecord + maxRecords);
		}

		resultList.setStartRecord(startRecord);
		resultList.setMaxRecords(maxRecords);
		resultList.setTotalRecords(totalRecords);
		resultList.setPage(page);

		resultList.setTotalPages(
				new BigDecimal(totalRecords).divide(new BigDecimal(maxRecords), BigDecimal.ROUND_UP).intValue());

		resultList.addAll(super.queryForList(pagingSql.toString(), elementType, param));

		return resultList;
	}

	@SuppressWarnings("hiding")
	public <T> PagingList<T> queryForPagingList(final String sql, final Class<T> elementType, StatementParameter param,
			final int... args) {
		final PagingList<T> resultList = new ArrayPagingList<T>();

		if (args == null || args.length < 2) {
			resultList.addAll(queryForList(sql, elementType, param));
			return resultList;
		}

		int startRecord = 0;
		if (args[0] > 0) {
			startRecord = args[0];
		}
		int maxRecords = 1;
		if (args[1] > 1) {
			maxRecords = args[1];
		}

		final int page = startRecord / maxRecords;

		final StringBuilder countSql = new StringBuilder("SELECT COUNT(*) FROM (").append(sql).append(") a");
		if (param == null) {
			param = new StatementParameter();
		}
		final int totalRecords = super.queryForInt(countSql.toString(), param);

		StringBuilder pagingSql = new StringBuilder("SELECT a.* FROM (").append(sql).append(") a");

		if (MYSQL_DIALECT.equals(dialect)) {
			pagingSql.append(" LIMIT ?, ?");
			param.setInt(startRecord);
			param.setInt(maxRecords);
		} else if (DB2_DIALECT.equals(dialect)) {
			final StringBuilder db2sql = new StringBuilder();
			db2sql.append("select * from (select ROW_NUMBER() OVER() AS ROWNUM, source.*  from (");
			db2sql.append(sql);
			db2sql.append(") source ) a where ROWNUM > ? and ROWNUM <= ?");

			pagingSql = db2sql;
			param.setInt(startRecord);
			param.setInt(startRecord + maxRecords);
		}

		resultList.setStartRecord(startRecord);
		resultList.setMaxRecords(maxRecords);
		resultList.setTotalRecords(totalRecords);
		resultList.setPage(page);

		resultList.setTotalPages(
				new BigDecimal(totalRecords).divide(new BigDecimal(maxRecords), BigDecimal.ROUND_UP).intValue());

		resultList.addAll(super.queryForList(pagingSql.toString(), elementType, param));

		return resultList;
	}

	protected String getOrderBySql(final Sort[] sorts, final String alias) {
		final StringBuilder sql = new StringBuilder();
		sql.append(" ORDER BY ");
		boolean flag = false;
		for (final Sort sort : sorts) {
			if (flag) {
				sql.append(", ");
			}

			if (StringUtils.isNotBlank(alias)) {
				sql.append(alias).append(".");
			}
			sql.append(underscoreName(sort.getColumn()));
			if (Sort.SortType.DESC.toStringValue().equals(sort.getSortType())) {
				sql.append(" DESC");
			}
			flag = true;
		}

		return sql.toString();
	}

	private class SqlStatementParameter {
		private String sql;
		private StatementParameter param;

		SqlStatementParameter(final String sql, final StatementParameter param) {
			setSql(sql);
			setParam(param);
		}

		public String getSql() {
			return sql;
		}

		public void setSql(final String sql) {
			this.sql = sql;
		}

		public StatementParameter getParam() {
			return param;
		}

		public void setParam(final StatementParameter param) {
			this.param = param;
		}

	}
}
